﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using ECS.DAL;
using ECS.DAL.Entity;

namespace ECS.DAL.Component
{
    public class MAWBComponent : BaseSQLModel
    {
        #region private properties
        private const string SQL_QUERY = " ";

        private const string SQL_PROC_QUERY = "usp_GetMAWB"; // @AppId,@MAWB, @Prefix, @Serial, @Device, @UserId ";

        private const string SQL_PARAM_SEARCH_SETUP_ID = " ";

        private const string SQL_INSERT = " ";

        private const string SQL_PROC_INSERT = "usp_SaveMAWB"; // @AppId, @MAWB, @Prefix, @Serial, @HAWB, @PartialSeq, @ShipmentType, " +
                                    //" @SlipType, @PartialAccept, @Carrier, @FlightNo, @FlightDate, @Destination, @AgentCode," +
                                    //"@AgentName, @NoG, @PieceCount, @EWS, @FBL, @Device, @UserId "; 

        private const string SQL_UPDATE = " ";

        private const string SQL_PROC_UPDATE = "usp_UpdateMAWBRecord";

        private const string SQL_DELETE = " ";
        #endregion

        #region public properties
        /// <summary>
        /// Error result
        /// </summary>
        public ErrorInfo ErrorMessage { set; get; }
        #endregion

        #region constructor
        public MAWBComponent()
        {
            ErrorMessage = new ErrorInfo();
        }
        #endregion

        #region private methods
        private void ClearErrorMessage()
        {
            ErrorMessage.Status = string.Empty;
            ErrorMessage.ErrorCode = string.Empty;
            ErrorMessage.Message = string.Empty;
        }
        #endregion

        #region public mehods
        /// <summary>
        /// get data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<ObjectContext> Search(ObjectContext parameter)
        {
            /*
             * @AppId,@MAWB, @Prefix, @Serial, @Device, @UserId
             */
            MAWB param = (MAWB)parameter;
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append(SQL_PROC_QUERY);
                //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(param.AppId));
                //sqlString.Replace("@MAWB", BaseSQLModel.SqlWithQoute(param.Mawb));
                //sqlString.Replace("@Prefix", BaseSQLModel.SqlWithQoute(param.MawbPrefix));
                //sqlString.Replace("@Serial", BaseSQLModel.SqlWithQoute(param.MawbSerial));
                //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(param.Device));
                //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(param.UserId));

                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", param.AppId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB", param.Mawb));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", param.MawbPrefix));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", param.MawbSerial));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", param.Device));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    MAWB data = new MAWB();
                    ErrorMessage.Status = base.GetString(0, reader);
                    ErrorMessage.ErrorCode = base.GetString(1, reader);
                    ErrorMessage.Message = base.GetString(2, reader);
                    ErrorMessage.Source = base.GetString(3, reader); //source
                    data.Mawb = base.GetString(4, reader);  //mawb_id
                    data.MawbPrefix = base.GetString(5, reader);    //mawb_prefix
                    data.MawbSerial = base.GetString(6, reader);    //mawb_sn
                    data.PartialSequence = (int)base.GetInt(7, reader); //awb_seq
                    data.PartialSequence = (int)base.GetInt(8, reader); //awb_move_seq
                    data.Hawb = base.GetString(9, reader);
                    data.PartialSequence = (int)base.GetInt(10, reader);
                    data.ShipmentType= base.GetString(7, reader);
                    data.SlipType= base.GetString(11, reader);
                    data.Carrier = base.GetString(12, reader);
                    data.FlightNumber = base.GetString(13, reader);  //flight_number
                    data.FlightDate = (DateTime)base.GetDate(14, reader); //flight_date_std
                    data.Destination = base.GetString(15, reader);  //destination
                    data.AgentCode = base.GetString(16, reader);  //agent_code
                    data.AgentName = base.GetString(17, reader);  //agent_name
                    data.NoG = base.GetString(18, reader);  //nature_of_goods
                    data.PieceCount = (int)base.GetInt(19, reader);  //piece_count
                    data.NotChangeObject();
                    items.Add(data);
                }
            }
            return items;
        }

        /// <summary>
        /// get data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public DataSet SearchDataSet(ObjectContext parameter)
        {
            /*
             * @AppId,@MAWB, @Prefix, @Serial, @Device, @UserId
             */
            DataSet ds = new DataSet();
            MAWB param = (MAWB)parameter;
            //List<ObjectContext> items = new List<Entity.ObjectContext>();
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append(SQL_PROC_QUERY);
                //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(param.AppId));
                //sqlString.Replace("@MAWB", BaseSQLModel.SqlWithQoute(param.Mawb));
                //sqlString.Replace("@Prefix", BaseSQLModel.SqlWithQoute(param.MawbPrefix));
                //sqlString.Replace("@Serial", BaseSQLModel.SqlWithQoute(param.MawbSerial));
                //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(param.Device));
                //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(param.UserId));

                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", param.AppId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB", param.Mawb));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", param.MawbPrefix));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", param.MawbSerial));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", param.Device));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
                DbDataAdapter adapter = entities.CreateDataAdapter("");
                adapter.SelectCommand = cmd;
                adapter.Fill(ds, "testGetMAWB");
            }
            return ds;
        }
        /// <summary>
        /// get standard flight date
        /// </summary>
        /// <param name="mawbId"></param>
        /// <returns></returns>
        public DateTime GetFlightDate(string mawbId)
        {
            //MAWB mawb = null;
            DateTime dt = DateTime.MinValue;
            using (Entities entities = new Entities())
            {
                entities.Open();
                DbCommand cmd = entities.CreateCommand("SELECT flight_date_std FROM t_eics_mawb WHERE mawb_id=@mawb_id");
                cmd.Parameters.Add(entities.CreateParameter("@mawb_id", mawbId));
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    dt = (DateTime)GetDate(0, reader); //flight_date_std
                }
            }
            return dt;
        }

        public override string GetInsertSQL(ObjectContext param)
        {
            /*@AppId, @MAWB, @Prefix, @Serial, @HAWB, @PartialSeq, @ShipmentType, " +
                                    " @SlipType, @PartialAccept, @Carrier, @FlightNo, @FlightDate, @Destination, @AgentCode," +
                                    "@AgentName, @NoG, @PieceCount, @EWS, @FBL, @Device, @UserId "; */
            MAWB parameter = (MAWB)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_INSERT);
            //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(parameter.AppId));
            //sqlString.Replace("@MAWB", BaseSQLModel.SqlWithQoute(parameter.Mawb));
            //sqlString.Replace("@Prefix", BaseSQLModel.SqlWithQoute(parameter.MawbPrefix));
            //sqlString.Replace("@Serial", BaseSQLModel.SqlWithQoute(parameter.MawbSerial));
            //sqlString.Replace("@HAWB", BaseSQLModel.SqlWithQoute(parameter.Hawb));
            //sqlString.Replace("@PartialSeq", BaseSQLModel.SqlWithoutQoute(parameter.PartialSequence));
            //sqlString.Replace("@ShipmentType", BaseSQLModel.SqlWithQoute(parameter.ShipmentType));
            //sqlString.Replace("@SlipType", BaseSQLModel.SqlWithQoute(parameter.SlipType));
            //sqlString.Replace("@PartialAccept", BaseSQLModel.SqlWithQoute(parameter.PartialAcceptFlag));
            //sqlString.Replace("@Carrier", BaseSQLModel.SqlWithQoute(parameter.Carrier));
            //sqlString.Replace("@FlightNo", BaseSQLModel.SqlWithQoute(parameter.FlightNumber));
            //sqlString.Replace("@FlightDate", parameter.FlightDate == null ? "NULL" : BaseSQLModel.SqlWithQoute(string.Format("{0:yyyy-MM-dd}", parameter.FlightDate)));
            //sqlString.Replace("@Destination", BaseSQLModel.SqlWithQoute(parameter.Destination));
            //sqlString.Replace("@AgentCode", BaseSQLModel.SqlWithQoute(parameter.AgentCode));
            //sqlString.Replace("@AgentName", BaseSQLModel.SqlWithQoute(parameter.AgentName));
            //sqlString.Replace("@NoG", BaseSQLModel.SqlWithQoute(parameter.NoG));
            //sqlString.Replace("@PieceCount", BaseSQLModel.SqlWithoutQoute(parameter.PieceCount));
            //sqlString.Replace("@EWS", BaseSQLModel.SqlWithQoute(parameter.EWS));
            //sqlString.Replace("@FBL", BaseSQLModel.SqlWithQoute(parameter.FBLFlightNumber));
            //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(parameter.Device));
            //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetUpdateSQL(ObjectContext param)
        {
            //store procedure
            //@in_vchApplicationID			VARCHAR(40), -- Required
            //@in_vchSetupID					VARCHAR(40) = NULL,  --Required
            //@in_vchType						VARCHAR(10),  -- Required
            //@in_vchStationID				VARCHAR(40),  -- Required
            //@in_vchSettingName				VARCHAR(50),  -- Required
            //@in_vchSettingValueString		VARCHAR(50), 
            //@in_fltSettingValueNumber		FLOAT, 
            //@in_dtSettingValueDateTime		DATETIME, 
            //@in_vchDevice				    NVARCHAR(50),  -- Required
            //@in_vchUserID				    NVARCHAR(20)  -- Required
            
            // sql command
            //"@AppId,  @setup_id, @type, @station_id, @setting_name, @setting_value_string, @setting_value_number, @setting_value_datetime, " +
            //"@Device, @UserId ";

            MAWB parameter = (MAWB)param;
            StringBuilder sqlString = new StringBuilder();
            //sqlString.Append(SQL_UPDATE);
            sqlString.Append(SQL_PROC_UPDATE);
            //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(parameter.AppId));
            //sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            //sqlString.Replace("@station_id", BaseSQLModel.SqlWithQoute(parameter.StationId));
            //sqlString.Replace("@type", BaseSQLModel.SqlWithQoute(parameter.SetupType));
            //sqlString.Replace("@setting_name", BaseSQLModel.SqlWithQoute(parameter.SettingName));
            //sqlString.Replace("@setting_value_string", BaseSQLModel.SqlWithQoute(parameter.ValueString));
            //sqlString.Replace("@setting_value_number", parameter.ValueNumber == null ? "NULL" : parameter.ValueNumber.ToString());
            //sqlString.Replace("@setting_value_datetime", parameter.ValueDate == null ? "NULL" : BaseSQLModel.SqlWithQoute(string.Format("{0:yyyy-MM-dd}", parameter.ValueDate)));
            ////sqlString.Replace("@create_date", BaseSQLModel.SqlWithQoute(parameter.CreateDate.ToString("yyyy-MM-dd")));
            ////sqlString.Replace("@create_by", BaseSQLModel.SqlWithQoute(parameter.CreateBy));
            //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(parameter.Device));
            //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetDeleteSQL(ObjectContext param)
        {
            EicsSetup parameter = (EicsSetup)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_DELETE);
            sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            return sqlString.ToString();
        }
        /// <summary>
        /// Insert to data source
        /// </summary>
        /// <param name="param"></param>
        public override void Insert(ObjectContext param)
        {
            DataSet ds = new DataSet();
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                MAWB parameter = (MAWB)param;
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetInsertSQL(param));
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));

                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB", parameter.Mawb));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", parameter.MawbPrefix));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", parameter.MawbSerial));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchHAWB", parameter.Hawb));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intMAWBPartialSeq", parameter.PartialSequence));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchShipmentType", parameter.ShipmentType));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipType", parameter.SlipType));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchPartialAcceptanceFlag", parameter.PartialAcceptFlag));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchCarrier", parameter.Carrier));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchFlightNumber", parameter.FlightNumber));
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", parameter.FlightDate));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDestination", parameter.Destination));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentCode ", parameter.AgentCode));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentName", parameter.AgentName));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchNatureOfGoods", parameter.NoG));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intPieceCount", parameter.PieceCount));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchEWSRequired", parameter.EWS));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchFBLFlightNumber", parameter.FBLFlightNumber));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                    DbDataAdapter adapter = entities.CreateDataAdapter(GetInsertSQL(param));
                    adapter.SelectCommand = cmd;
                    adapter.Fill(ds, "Result");
                    //entities.SetCommand(cmd);
                    //int m_iRecordChanged = entities.SaveChanges();
                    //if (m_iRecordChanged < 1)
                    if(ds.Tables[0].Rows.Count > 0)
                    {
                        ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                        ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                        ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                        ErrorMessage.ID = ds.Tables[0].Rows[0]["mawb_id"].ToString();
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// update to datasource
        /// </summary>
        /// <param name="param"></param>
        public override void Update(ObjectContext param)
        {
            MAWB parameter = (MAWB)param;
            DataSet ds = new DataSet();
            ClearErrorMessage();
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetUpdateSQL(param));
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBID", parameter.MawbId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB", parameter.Mawb));

                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", parameter.MawbPrefix));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", parameter.MawbSerial));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intAWBSeq", parameter.AWBSequence));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intAWBMoveSeq", parameter.AWBMoveSequence));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchHAWB", parameter.Hawb));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intMAWBPartialSeq", parameter.PartialSequence));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));

                    cmd.Parameters.Add(entities.CreateParameter("@in_vchCarrier", parameter.Carrier));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchFlightNumber", parameter.FlightNumber));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDestination", parameter.Destination));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentCode", parameter.AgentCode));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentName", parameter.AgentName));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchNatureOfGoods", parameter.NoG));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intPieceCount", parameter.PieceCount));
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", parameter.FlightDate));

                    DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                    adapter.Fill(ds, "Result");
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                        ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                        ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                        //if (ErrorMessage.Status == "PASS")
                        //{
                        //    ErrorMessage.ID = ds.Tables[0].Rows[0]["mawb_id"].ToString();
                        //}
                        
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// delete from datasource
        /// </summary>
        public override void Delete(ObjectContext param)
        {
            DataSet ds = new DataSet();
            MAWB parameter = (MAWB)param;
            ErrorMessage.Status = string.Empty;
            ErrorMessage.ErrorCode = string.Empty;
            ErrorMessage.Message = string.Empty;
           
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entity = new Entities())
                {
                    entity.Open();
                    DbCommand cmd = entity.CreateCommand("usp_DeleteMAWBRecord");
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(entity.CreateParameter("@in_vchApplicationID", parameter.AppId));
                    cmd.Parameters.Add(entity.CreateParameter("@in_vchMAWB_ID", parameter.MawbId));
                    cmd.Parameters.Add(entity.CreateParameter("@in_vchDevice", parameter.Device));
                    cmd.Parameters.Add(entity.CreateParameter("@in_vchUserID", parameter.UserId));
                    DbDataAdapter adapter = entity.CreateDataAdapter(cmd);
                    adapter.Fill(ds);
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    {
                        ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                        ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                        ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                    }
                }
                transaction.Complete();
            }
        }
        ///// <summary>
        ///// special case for store procedure to update data that return result as record
        ///// </summary>
        ///// <param name="items"></param>
        //public override void SaveChanges(List<ObjectContext> items)
        //{
        //    string sqlCmd = string.Empty;
        //    string textMessage = string.Empty;
        //    using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
        //    {
        //        using (Entities entities = new Entities())
        //        {
        //            entities.Open();
        //            DataSet ds = new DataSet();
        //            DbCommand cmd = entities.CreateCommand(sqlCmd);
                    
        //            DbDataAdapter adapter = entities.CreateDataAdapter(sqlCmd);
        //            adapter.SelectCommand = cmd;
        //            //cmd.CommandType = CommandType.StoredProcedure;
        //            foreach (ObjectContext item in items)
        //            {
        //                switch (item.GetObjectSate())
        //                {
        //                    case OBJECT_STATE.Insert:
        //                        sqlCmd = GetInsertSQL(item);
        //                        textMessage = "Insert ";
        //                        break;
        //                    case OBJECT_STATE.Update:
        //                        sqlCmd = GetUpdateSQL(item);
        //                        textMessage = "Update ";
        //                        break;
        //                    case OBJECT_STATE.Delete:
        //                        sqlCmd = GetDeleteSQL(item);
        //                        textMessage = "Delete ";
        //                        break;
        //                    default:
        //                        sqlCmd = string.Empty;
        //                        break;
        //                }
        //                if (!string.IsNullOrEmpty(sqlCmd))
        //                {
        //                    //DbCommand cmd = entities.CreateCommand(sqlCmd);
        //                    //cmd.CommandType = CommandType.StoredProcedure;
        //                    //entities.SetCommand(cmd);
        //                    //int m_iRecordChanged = entities.SaveChanges();
        //                    //int m_iRecordChanged = cmd.ExecuteNonQuery();
        //                    //if (m_iRecordChanged < 0)
        //                    //{
        //                    //    throw new Exception(string.Format("{0} transaction failed", textMessage));
        //                    //}
        //                    cmd.CommandText = sqlCmd;
        //                    adapter.Fill(ds);
        //                    if (ds.Tables[0].Rows.Count > 0)
        //                    {
        //                        if (!ds.Tables[0].Rows[0]["Status"].ToString().Equals("PASS"))
        //                        {
        //                            string errorMessage = string.Format("Status: {0} Error Code:{1}\r\nError:{2}", ds.Tables[0].Rows[0]["Status"].ToString(), ds.Tables[0].Rows[0]["ErrorCode"].ToString(), ds.Tables[0].Rows[0]["Message"].ToString());
        //                            throw new Exception(errorMessage);
        //                        }
        //                    }
        //                    item.NotChangeObject();
        //                }
        //            }
        //        }
        //        transaction.Complete();
        //    }
        //}

        public void SaveMAWBSet(MAWB mawb, WeightSlip slip, List<ObjectContext> weights, List<ObjectContext> dimensions, List<ObjectContext> comments)
        {
            string dataTableName = string.Empty;
            string sqlCmd = string.Empty;
            string textMessage = string.Empty;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DataSet ds = new DataSet();
                    DbCommand cmd = entities.CreateCommand();
                    //MAWB
                    switch(mawb.GetObjectSate())
                    {
                        case OBJECT_STATE.Insert:
                            sqlCmd = GetInsertSQL(mawb);
                            break;
                        case OBJECT_STATE.Update:
                            sqlCmd = GetUpdateSQL(mawb);
                            break;
                        case OBJECT_STATE.Delete:
                            sqlCmd = GetDeleteSQL(mawb);
                            break;
                    }
                    
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sqlCmd;
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", mawb.AppId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB", mawb.Mawb));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBPrefix", mawb.MawbPrefix));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWBSerial", mawb.MawbSerial));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchHAWB", mawb.Hawb));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intMAWBPartialSeq", mawb.PartialSequence));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchShipmentType", mawb.ShipmentType));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipType", mawb.SlipType));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchPartialAcceptanceFlag", mawb.PartialAcceptFlag));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchCarrier", mawb.Carrier));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchFlightNumber", mawb.FlightNumber));
                    cmd.Parameters.Add(entities.CreateParameter("@in_dtFlightDate", mawb.FlightDate));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDestination", mawb.Destination));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentCode ", mawb.AgentCode));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchAgentName", mawb.AgentName));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchNatureOfGoods", mawb.NoG));
                    cmd.Parameters.Add(entities.CreateParameter("@in_intPieceCount", mawb.PieceCount));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchEWSRequired", mawb.EWS));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchFBLFlightNumber", mawb.FBLFlightNumber));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", mawb.Device));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", mawb.UserId));

                    DbDataAdapter adapter = entities.CreateDataAdapter(sqlCmd);
                    adapter.SelectCommand = cmd;
                    dataTableName = "MAWB";
                    adapter.Fill(ds, dataTableName);
                    if (ds.Tables[dataTableName].Rows.Count > 0)
                    {
                        if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                        {
                            ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                            ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                            ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                            throw new Exception(ErrorMessage.Message);
                        }
                    }

                    //Weight slip
                    WeightSlipComponent ws = new WeightSlipComponent();
                    switch (slip.GetObjectSate())
                    {
                        case OBJECT_STATE.Insert:
                            sqlCmd =  ws.GetInsertSQL(slip);
                            break;
                        case OBJECT_STATE.Update:
                            sqlCmd = ws.GetUpdateSQL(slip);
                            break;
                        case OBJECT_STATE.Delete:
                            sqlCmd = ws.GetDeleteSQL(mawb);
                            break;
                    }
                    cmd.CommandText = sqlCmd;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", slip.AppId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchMAWB_ID", slip.MawbId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchScaleID", slip.ScaleId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchStationID", slip.StationId));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipNumber", slip.SlipNo));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", slip.Device));
                    cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", slip.UserId));
                    adapter.SelectCommand = cmd;
                    dataTableName = "Slip";
                    adapter.Fill(ds, dataTableName);
                    if (ds.Tables[dataTableName].Rows.Count > 0)
                    {
                        if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                        {
                            ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                            ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                            ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                            throw new Exception(ErrorMessage.Message);
                        }
                    }
                    
                    //Weight record
                    dataTableName = "WeightRecord";
                    WeightRecordComponent wrc = new WeightRecordComponent();
                    WeightRecord wr = null;
                    foreach (ObjectContext item in weights)
                    {
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                sqlCmd = wrc.GetInsertSQL(item);
                                textMessage = "Insert ";
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = wrc.GetUpdateSQL(item);
                                textMessage = "Update ";
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = wrc.GetDeleteSQL(item);
                                textMessage = "Delete ";
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            wr = (WeightRecord)item;
                            cmd.CommandText = sqlCmd;
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", wr.AppId));
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", wr.SlipId));
                            cmd.Parameters.Add(entities.CreateParameter("@in_intWeightNumber", wr.WeightNumber));
                            cmd.Parameters.Add(entities.CreateParameter("@in_fltGrossWeight", wr.GrossWeight));
                            cmd.Parameters.Add(entities.CreateParameter("@in_fltTareWeight", wr.TareWeight));
                            cmd.Parameters.Add(entities.CreateParameter("@in_fltNetWeight", wr.NetWeight));
                            cmd.Parameters.Add(entities.CreateParameter("@in_intPalletCount", wr.PalletCount));
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", wr.Device));
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", wr.UserId));
                            adapter.Fill(ds, dataTableName);
                            if (ds.Tables[dataTableName].Rows.Count > 0)
                            {
                                if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                                {
                                    ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                                    ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                                    ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                                    throw new Exception(ErrorMessage.Message);
                                }
                            }
                            //item.NotChangeObject();
                        }
                    }

                    //Dimensions
                    dataTableName = "Dimensions";
                    DimensionRecordComponent drc = new DimensionRecordComponent();
                    DimensionRecord dr = null;
                    foreach (ObjectContext item in dimensions)
                    {
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                sqlCmd = drc.GetInsertSQL(item);
                                textMessage = "Insert ";
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = drc.GetUpdateSQL(item);
                                textMessage = "Update ";
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = drc.GetDeleteSQL(item);
                                textMessage = "Delete ";
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            dr = (DimensionRecord)item;
                            cmd.CommandText = sqlCmd;
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", dr.AppId));
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", dr.SlipId));
                            cmd.Parameters.Add(entities.CreateParameter("@in_intDimensionNumber", dr.DimensionNumber));
                            cmd.Parameters.Add(entities.CreateParameter("@in_fltLength", dr.Length));
                            cmd.Parameters.Add(entities.CreateParameter("@in_fltWidth", dr.Width));
                            cmd.Parameters.Add(entities.CreateParameter("@in_fltHeight", dr.Height));
                            cmd.Parameters.Add(entities.CreateParameter("@in_intPieceCount", dr.PieceCount));
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", dr.Device));
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", dr.UserId));

                            adapter.Fill(ds, dataTableName);
                            if (ds.Tables[dataTableName].Rows.Count > 0)
                            {
                                if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                                {
                                    ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                                    ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                                    ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                                    throw new Exception(ErrorMessage.Message);
                                }
                            }
                            //item.NotChangeObject();
                        }
                    }

                    //Comment
                    dataTableName = "Comment";
                    CommentRecordComponent crc = new CommentRecordComponent();
                    CommentRecord cr = null;
                    foreach (ObjectContext item in comments)
                    {
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                sqlCmd = crc.GetInsertSQL(item);
                                textMessage = "Insert ";
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = crc.GetUpdateSQL(item);
                                textMessage = "Update ";
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = crc.GetDeleteSQL(item);
                                textMessage = "Delete ";
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            cr = (CommentRecord)item;
                            cmd.CommandText = sqlCmd;
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", cr.AppId));
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", cr.SlipId));
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchCommentTypeID", cr.CommentTypeId));
                            cmd.Parameters.Add(entities.CreateParameter("@in_intCommentNumber", cr.CommentNumber));
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchValue", cr.Value));
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", cr.Device));
                            cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", cr.UserId));

                            adapter.Fill(ds, dataTableName);
                            if (ds.Tables[dataTableName].Rows.Count > 0)
                            {
                                if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                                {
                                    ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                                    ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                                    ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                                    throw new Exception(ErrorMessage.Message);
                                }
                            }
                            //item.NotChangeObject();
                        }
                    }

                    mawb.NotChangeObject();
                    slip.NotChangeObject();
                    foreach (WeightRecord item in weights)
                    {
                        item.NotChangeObject();
                    }
                    foreach (DimensionRecord item in dimensions)
                    {
                        item.NotChangeObject();
                    }
                    foreach (CommentRecord item in comments)
                    {
                        item.NotChangeObject();
                    }
                }
            }
        }
        #endregion
    }
}
